// use crate::model::Fund;
use anyhow::anyhow;
use anyhow::Ok;
use anyhow::Result;
use surrealdb::opt::PatchOp;

use crate::model::trade::trade_tx_of::TradeTx;
use crate::model::FudaRecord;
use crate::model::trade::trade_tx_of;

use crate::db::FUDB;
use crate::db::FUHLC;

///
/// update or create a trade_tx record
///
/// @param trade_tx: trade_tx's basic information
///
pub async fn upsert(trade_tx: &trade_tx_of::TradeTx) -> Result<Option<FudaRecord>> {
    let id = trade_tx_of::TradeTx::id_from(
        &trade_tx.security_id,
        &trade_tx.settlement_date,
        &trade_tx.settlement_time
    )?;

    let old_trade_tx: Option<trade_tx_of::TradeTx> = FUDB.select(("trade_tx", id.as_str())).await?;

    let mut updated: Option<FudaRecord> = None;
    let mut to_update = false;
    let timestamp = FUHLC.new_timestamp().to_string();
    // Update a record with a specific ID
    let mut patch = FUDB.update(("trade_tx", &id)).patch(
        PatchOp::replace("/last_updated", timestamp)
    );

    match old_trade_tx {
        Some(TradeTx { id: Some(t), .. }) => {
            updated = Some(FudaRecord { id: t.into() });
        }
        _ => {
            patch = patch.patch(PatchOp::replace("/security_id", &trade_tx.security_id));
            patch = patch.patch(PatchOp::replace("/settlement_date", &trade_tx.settlement_date));
            patch = patch.patch(PatchOp::replace("/settlement_time", &trade_tx.settlement_time));

            to_update = true;
        }
    }

    if let Some(v) = &trade_tx.security_name {
        patch = patch.patch(PatchOp::replace("/security_name", v));
        to_update = true;
    }

    if let Some(v) = &trade_tx.security_code {
        patch = patch.patch(PatchOp::replace("/security_code", v));
        to_update = true;
    }

    if let Some(v) = &trade_tx.summary_info {
        patch = patch.patch(PatchOp::replace("/summary_info", v));
        to_update = true;
    }

    if let Some(v) = &trade_tx.trade_quantity {
        patch = patch.patch(PatchOp::replace("/trade_quantity", v));
        to_update = true;
    }

    if let Some(v) = &trade_tx.trade_price {
        patch = patch.patch(PatchOp::replace("/trade_price", v));
        to_update = true;
    }

    if let Some(v) = &trade_tx.trade_amount {
        patch = patch.patch(PatchOp::replace("/trade_amount", v));
        to_update = true;
    }

    if let Some(v) = &trade_tx.occurred_amount {
        patch = patch.patch(PatchOp::replace("/occurred_amount", v));
        to_update = true;
    }

    if let Some(v) = &trade_tx.credit_delegation_type {
        patch = patch.patch(PatchOp::replace("/credit_delegation_type", v));
        to_update = true;
    }

    if let Some(v) = &trade_tx.contract_number {
        patch = patch.patch(PatchOp::replace("/contract_number", v));
        to_update = true;
    }

    if let Some(v) = &trade_tx.trading_market {
        patch = patch.patch(PatchOp::replace("/trading_market", v));
        to_update = true;
    }

    if let Some(v) = &trade_tx.shareholder_code {
        patch = patch.patch(PatchOp::replace("/shareholder_code", v));
        to_update = true;
    }

    if let Some(v) = &trade_tx.exchange_rate {
        patch = patch.patch(PatchOp::replace("/exchange_rate", v));
        to_update = true;
    }

    if let Some(v) = &trade_tx.stamp_duty {
        patch = patch.patch(PatchOp::replace("/stamp_duty", v));
        to_update = true;
    }

    if let Some(v) = &trade_tx.net_commission {
        patch = patch.patch(PatchOp::replace("/net_commission", v));
        to_update = true;
    }

    if let Some(v) = &trade_tx.transfer_fee {
        patch = patch.patch(PatchOp::replace("/transfer_fee", v));
        to_update = true;
    }

    if let Some(v) = &trade_tx.clearing_fee {
        patch = patch.patch(PatchOp::replace("/clearing_fee", v));
        to_update = true;
    }

    if let Some(v) = &trade_tx.trading_fee {
        patch = patch.patch(PatchOp::replace("/trading_fee", v));
        to_update = true;
    }

    if let Some(v) = &trade_tx.regulatory_fee {
        patch = patch.patch(PatchOp::replace("/regulatory_fee", v));
        to_update = true;
    }
    if let Some(v) = &trade_tx.other_fees {
        patch = patch.patch(PatchOp::replace("/other_fees", v));
        to_update = true;
    }
    if let Some(v) = &trade_tx.front_desk_fee {
        patch = patch.patch(PatchOp::replace("/front_desk_fee", v));
        to_update = true;
    }
    if let Some(v) = &trade_tx.total_fees {
        patch = patch.patch(PatchOp::replace("/total_fees", v));
        to_update = true;
    }
    if let Some(v) = &trade_tx.combination_type {
        patch = patch.patch(PatchOp::replace("/combination_type", v));
        to_update = true;
    }

    if to_update {
        updated = patch.await.map_err(|err| anyhow!(err))?;
    }

    Ok(updated)
}

///
/// delete trade_tx by it's id , e.g. SH.STK.600519
///
pub async fn delete(trade_tx_id: &str) -> Result<trade_tx_of::TradeTx> {
    let rs: Option<trade_tx_of::TradeTx> = FUDB.delete(("trade_tx", trade_tx_id)).await?;
    match rs {
        Some(trade_tx) => Ok(trade_tx),
        None => Err(anyhow!(format!("Failed to delete, TradeTx not found for {}", trade_tx_id))),
    }
}

/// select trade_strategies
///
/// klt: [Klt](super::kline::Klt)
///
/// pi: u16, page index, start from 0
///
/// pn: u16, page size,
///
pub async fn select(
    security_id: Option<String>,
    pi: u16,
    pn: u16
) -> Result<Vec<trade_tx_of::TradeTx>> {
    let start = pi * pn;

    let q_str = match &security_id {
        Some(_) =>
            format!(
                "SELECT * FROM trade_tx WHERE security_id=$security_id LIMIT {} START {}",
                pn,
                start
            ),
        None => format!("SELECT * FROM trade_tx LIMIT {} START {}", pn, start),
    };

    // Fetch kline data within the specified date range using bind variables
    let mut query = FUDB.query(q_str);

    if let Some(t_) = &security_id {
        query = query.bind(("security_id", t_)); // Bind the start_date variable
    }

    // println!("query={:?}", query);

    let mut response = query.await?;

    // println!("\nresponse= {:?}\n", response);

    let values: Vec<trade_tx_of::TradeTx> = response.take(0)?;

    Ok(values)
}

#[cfg(test)]
mod test {
    use chrono::Duration;
    use chrono::Utc;
    use chrono::TimeZone;

    use crate::db;
    use crate::model::trade::trade_tx_of;
    use crate::db::FUHLC;

    #[tokio::test]
    async fn test_upsert_trade_tx() {
        let _ = db::init().await.unwrap();

        for i in 0..3 {
            let trade_tx = trade_tx_of::TradeTx {
                security_id: "SH.STOCK.600519".to_string(),
                security_name: Some("todo".to_string()),
                security_code: Some("600519".to_string()),
                summary_info: Some("todo".to_string()),
                trade_quantity: Some(123),
                trade_price: Some(100.0),
                trade_amount: Some(12300.0),
                occurred_amount: Some(222.0),
                credit_delegation_type: Some("todo".to_string()),
                settlement_date: format!(
                    "{}",
                    (Utc.ymd(2020, 1, 1).and_hms(0, 0, 0) + Duration::days(i)).format("%Y%m%d")
                ),
                contract_number: Some("todo".to_string()),
                trading_market: Some("todo".to_string()),
                shareholder_code: Some("todo".to_string()),
                exchange_rate: Some(8.0),
                stamp_duty: Some(1.2),
                net_commission: Some(2.0),
                transfer_fee: Some(3.0),
                clearing_fee: Some(0.5),
                trading_fee: Some(1.0),
                regulatory_fee: Some(2.2),
                other_fees: Some(0.0),
                front_desk_fee: Some(0.0),
                total_fees: Some(12.0),
                settlement_time: format!(
                    "{}",
                    (Utc.ymd(2020, 1, 1).and_hms(0, 0, 0) + Duration::minutes(i)).format("%H:%M:%S")
                ),
                combination_type: Some("todo".to_string()),
                ..Default::default()
            };

            // Update a trade_tx record with a specific id
            let res = db::trade::trade_tx_of::upsert(&trade_tx).await;

            println!("res {:?}", res);
        }
    }

    /// Need to call first: test_update_kline_from_eastmoney to update klines for "513130"
    #[tokio::test]
    async fn test_select_trade_txs() {
        // generate a timestamp
        let ts = FUHLC.new_timestamp();
        println!("\nts = {}", ts.to_string());

        let _ = db::init().await.unwrap();

        let result = db::trade::trade_tx_of::select(None, 0, 10).await;

        println!("result= {:?}\n", result);
        assert_eq!(result.is_ok(), true);

        if let Ok(values) = &result {
            for (index, value) in values.iter().enumerate() {
                println!(
                    "[{:02}] security_id={:?}, settlement_date={}, settlement_time={} ",
                    index,
                    value.security_id,
                    value.settlement_date,
                    value.settlement_time
                );
            }
        }
    }
}
